/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tkgarment.system.dao;

import com.tkgarment.system.domain.Report;
import com.tkgarment.system.factory.DAOFactory;
import com.tkgarment.system.inf.IDAO;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 *
 * @author user
 */
public class ReportDAO extends AbstractDAO implements IDAO<Report, String> {

    
    public List<Report> getReportBirthday()throws Exception{
        String sql = "SELECT "
                + " emp_id,name_th,surname_th,birth_date,department_name"
                + " FROM "
                + " emp_master inner join department "
                + " on emp_master.department_id  = department.department_id"
                + " where  date_part('month', birth_date) in (date_part('month',now()),date_part('month',now())+1)";
        if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }

        List<Map<String, Object>> result = this.getCon().queryList(sql);
        List<Report> rep = new ArrayList<>();
        DateFormat dmt = new SimpleDateFormat("yyyy-MM-dd");
        DateFormat dmt2 = new SimpleDateFormat("dd/MM/yyyy");
        for (Map<String, Object> map : result) {
            Report report = new Report();
            report.setField1(map.get("emp_id").toString());
            report.setField2(map.get("name_th").toString());
            report.setField3(map.get("surname_th").toString());
            report.setField4(dmt2.format(dmt.parse(map.get("birth_date").toString())));
            report.setField5(map.get("department_name").toString());
            rep.add(report);
        }

        return rep;
    }
    
    
    public List<Report> getReportEmpType() throws Exception {
        String sql = "SELECT "
                + "  emp_type.emp_type_name,count(emp_master.emp_id) as vol"
                + " FROM "
                + "   public.emp_type left join public.emp_master "
                + " on "
                + "  emp_type.emp_type_id = emp_master.emp_type_id"
                + " GROUP BY emp_type.emp_type_name";
         if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }

        List<Map<String, Object>> result = this.getCon().queryList(sql);
        List<Report> rep = new ArrayList<>();
        for (Map<String, Object> map : result) {
            Report report = new Report();
            report.setField1(map.get("emp_type_name").toString());
            report.setField2(map.get("vol").toString());
            rep.add(report);
        }

        return rep;
    }

    
    public List<Report> getReportGraduation() throws Exception {
        String sql = "SELECT "
                + "  graduation.level_name,count(emp_master.emp_id) as vol"
                + " FROM "
                + "  public.graduation left join"
                + "  public.emp_master"
                + " on"
                + "  graduation.graduate_level_id = emp_master.graduate_level_id"
                + " GROUP BY graduation.level_name";
        if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }

        List<Map<String, Object>> result = this.getCon().queryList(sql);
        List<Report> rep = new ArrayList<>();
        for (Map<String, Object> map : result) {
            Report report = new Report();
            report.setField1(map.get("level_name").toString());
            report.setField2(map.get("vol").toString());
            rep.add(report);
        }

        return rep;
    }

    public List<Report> getReportSex() throws Exception {
        String sql = "SELECT "
                + "  sex.sexname,count(emp_id) as vol"
                + " FROM "
                + "  public.sex, "
                + "  public.emp_master"
                + " WHERE "
                + "  emp_master.sex_id = sex.sexid"
                + " group by emp_master.sex_id,sex.sexname";
        if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }

        List<Map<String, Object>> result = this.getCon().queryList(sql);
        List<Report> rep = new ArrayList<>();
        for (Map<String, Object> map : result) {
            Report report = new Report();
            report.setField1(map.get("sexname").toString());
            report.setField2(map.get("vol").toString());
            rep.add(report);
        }

        return rep;
    }

    @Override
    public List<Report> getAll() throws Exception {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public int updateSingle(Report t, String id) throws Exception {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public int deleteSingle(Report t) throws Exception {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public int insertSingle(Report t) throws Exception {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public Report getById(String id) throws Exception {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    public List<Report> getReportSex(String filter) {
         String sql = "SELECT "
                + "  sex.sexname,count(emp_id) as vol"
                + " FROM "
                + "  public.sex, "
                + "  public.emp_master"
                + " WHERE "
                + "  emp_master.sex_id = sex.sexid and emp_master.department_id = '" + filter +  "'"
                + " group by emp_master.sex_id,sex.sexname";
        if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }

        List<Map<String, Object>> result = this.getCon().queryList(sql);
        List<Report> rep = new ArrayList<>();
        for (Map<String, Object> map : result) {
            Report report = new Report();
            report.setField1(map.get("sexname").toString());
            report.setField2(map.get("vol").toString());
            rep.add(report);
        }

        return rep;
    }

    public List<Report> getReportEmpType(String filter) {
         String sql = "SELECT "
                + "  emp_type.emp_type_name,count(emp_master.emp_id) as vol"
                + " FROM "
                + "   public.emp_type left join public.emp_master "
                + " on "
                + "  emp_type.emp_type_id = emp_master.emp_type_id where emp_master.department_id = '" + filter + "'"
                + " GROUP BY emp_type.emp_type_name";
         if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }

        List<Map<String, Object>> result = this.getCon().queryList(sql);
        List<Report> rep = new ArrayList<>();
        for (Map<String, Object> map : result) {
            Report report = new Report();
            report.setField1(map.get("emp_type_name").toString());
            report.setField2(map.get("vol").toString());
            rep.add(report);
        }

        return rep;
    }

    public List<Report> getReportGraduation(String filter) {
        String sql = "SELECT "
                + "  graduation.level_name,count(emp_master.emp_id) as vol"
                + " FROM "
                + "  public.graduation left join"
                + "  public.emp_master"
                + " on"
                + "  graduation.graduate_level_id = emp_master.graduate_level_id where emp_master.department_id = '"+ filter +"'"
                + " GROUP BY graduation.level_name";
        if (this.getCon() == null) {
            this.setCon(DAOFactory.creatConnection());
        }

        List<Map<String, Object>> result = this.getCon().queryList(sql);
        List<Report> rep = new ArrayList<>();
        for (Map<String, Object> map : result) {
            Report report = new Report();
            report.setField1(map.get("level_name").toString());
            report.setField2(map.get("vol").toString());
            rep.add(report);
        }

        return rep;
    }

}
